#region using
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

using DbmlManager.Lib.Utility;
#endregion

namespace DbmlManager.Lib.Database
{
  public class SqlDbCreator : IDbCreator
  {
    // Constructors

    #region SqlDbCreator(IDbConnectionInfo dbci)
    public SqlDbCreator(IDbConnectionInfo dbci)
    {
      // Note: We only use the dbci passed in as a baseline.  We
      // make a copy of it and change the DbName to "master"
      // so we can open a connection to the master database on
      // the SQL Server;

      _dbConnectionInfo = new DbConnectionInfo(dbci);
      _dbConnectionInfo.DbName = "master";
    }
    #endregion

    // Private Properties

    #region Connection
    private IDbConnection Connection
    {
      get
      {
        return _connection;
      }
    }
    IDbConnection _connection = null;
    #endregion

    // Private Methods

    #region Open()
    private void Open()
    {
      _connection = new SqlConnection(ConnectionString);
      Connection.Open();
    }
    #endregion

    #region Close()
    private void Close()
    {
      Connection.Close();
      Connection.Dispose();
      _connection = null;
    }
    #endregion

    #region DbConnectionInfo
    private IDbConnectionInfo DbConnectionInfo
    {
      get
      {
        return _dbConnectionInfo;
      }
    }
    private IDbConnectionInfo _dbConnectionInfo = null;
    #endregion

    // IDbCreator Implementation - Properties

    #region ServerName
    public string ServerName
    {
      get
      {
        return DbConnectionInfo.ServerName;
      }
      set
      {
        DbConnectionInfo.ServerName = value;
      }
    }
    #endregion

    #region Password
    public string Password
    {
      get
      {
        return DbConnectionInfo.Password;
      }
      set
      {
        DbConnectionInfo.Password = value;
      }
    }
    private string _password = String.Empty;
    #endregion

    #region ConnectionString
    public string ConnectionString
    {
      get
      {
        return DbConnectionInfo.ConnectionString;
      }
    }
    #endregion

    #region UserId
    public string UserId
    {
      get
      {
        return DbConnectionInfo.UserName;
      }
      set
      {
        DbConnectionInfo.UserName = value;
      }
    }
    #endregion

    #region ErrorMessage
    public string ErrorMessage
    {
      get
      {
        return _errorMessage;
      }
    }
    private string _errorMessage = null;
    #endregion
    
    // IDbCreator Implementation - Methods

    #region GetCreateDbSql(string dbname)
    public string GetCreateDbSql(string dbname)
    {
      return "create database " + dbname;
    }
    #endregion
    
    #region CreateDb(string dbname)
    public void CreateDb(string dbname)
    {
      string sql = GetCreateDbSql(dbname);

      try
      {
        Open();

        using (IDbCommand cmd = Connection.CreateCommand())
        {
          cmd.CommandText = sql;
          cmd.ExecuteNonQuery();
        }

        Close();
      }
      catch // (Exception e)
      {
        //if (ExceptionPolicy.HandleException(e, "/Core/Database/DbCreator"))
        //  throw;
      }
    }
    #endregion

    #region EnsureDb(string dbname)
    public void EnsureDb(string dbname)
    {
      if (!DbExists(dbname))
        CreateDb(dbname);
    }
    #endregion

    #region GetDatabases()
    public List<string> GetDatabases()
    {
      return GetDatabases(false);
    }
    #endregion

    #region GetDatabases(bool includeSysDbs)
    public List<string> GetDatabases(bool includeSysDbs)
    {
      List<string> databases = new List<string>();

      string sql = "select name from sysdatabases order by name";

      try
      {
        Open();

        using (IDbCommand cmd = Connection.CreateCommand())
        {
          cmd.CommandText = sql;
          using (IDataReader reader = cmd.ExecuteReader())
          {
            while (reader.Read())
            {
              string dbname = reader["name"] as string;

              if (!includeSysDbs &&
                  (dbname == "master" || dbname == "tempdb" ||
                   dbname == "model" || dbname == "mssqlweb" || dbname == "msdb"))
                continue;

              databases.Add(reader["name"] as string);
            }
          }
        }
      }
      catch // (Exception e)
      {
        //if (ExceptionPolicy.HandleException(e, "/Core/Database/DbCreator"))
        //  throw;
      }
      finally
      {
        Close();
      }

      return databases;
    }
    #endregion

    #region DbExists(string dbname)
    public bool DbExists(string dbname)
    {
      bool result = false;
      string sql = "select * from sysdatabases where name = @dbname";

      try
      {
        Open();

        using (IDbCommand cmd = Connection.CreateCommand())
        {
          cmd.CommandText = sql;
          IDataParameter param = cmd.CreateParameter();
          param.ParameterName = "@dbname";
          param.Value = dbname;
          cmd.Parameters.Add(param);

          using (IDataReader reader = cmd.ExecuteReader())
          {
            result = reader.Read();
          }
        }
      }
      catch // (Exception e)
      {
        //if (ExceptionPolicy.HandleException(e, "/Core/Database/DbCreator"))
        //  throw;
      }
      finally
      {
        Close();
      }

      return result;
    }
    #endregion

    #region DropDbIfExists(string dbname)
    public void DropDbIfExists(string dbname)
    {
      if (DbExists(dbname))
        DropDb(dbname);
    }
    #endregion

    #region DropDb(string dbname)
    public void DropDb(string dbname)
    {
      string sql = "drop database " + dbname;

      try
      {
        Open();

        using (IDbCommand cmd = Connection.CreateCommand())
        {
          cmd.CommandText = sql;
          cmd.ExecuteNonQuery();
        }

        Close();
      }
      catch // (Exception e)
      {
        //if (ExceptionPolicy.HandleException(e, "/Core/Database/DbCreator"))
        //  throw;
      }
    }
    #endregion

    #region EnsureNewDb(string dbname)
    public void EnsureNewDb(string dbname)
    {
      DropDbIfExists(dbname);
      CreateDb(dbname);
    }
    #endregion

    #region TestConnection()
    public bool TestConnection()
    {
      try
      {
        Open();
        return true;
      }
      catch (Exception e)
      {
        _errorMessage = e.Message;
        return false;
      }
    }
    #endregion
  }
}
